set more off
use "$datatemp/birthplace_codes.dta", clear
sort Birthplace_code
save "$datatemp/birthplace_codes.dta", replace

* Number of individuals born between 1990 and 1993 by oblast
import excel using "$data/numbirth_oblast_90.xlsx", clear firstrow sheet("Sheet1") cellrange(B4:G102)
drop if Birthplace_code==.
reshape long numbir_, i(Birthplace_code) j(Year)
sort Birthplace_code Year
save "$datatemp/numbirth_90.dta", replace
* Number of women of different age groups between 1990 and 1993 by oblast
import excel using "$data/numwomen_oblast_90.xlsx", clear firstrow sheet("Sheet1") cellrange(C7:AF115)
drop if Birthplace_code==.
forvalues i = 1990/1993 {
gen numwomen1544_`i' = numwomen15_`i'+numwomen20_`i'+numwomen25_`i'+numwomen30_`i'+numwomen35_`i'+numwomen40_`i'
}
reshape long numwomen1544_, i(Birthplace_code) j(Year)
keep Birthplace_code Year Birthplace numwomen1544_
sort Birthplace_code Year
save "$datatemp/numwomen1544_90.dta", replace

use "$datatemp/numbirth_90.dta", clear
merge (Birthplace_code Year) using "$datatemp/numwomen1544_90.dta"
keep if _merge ==3 
rename numbir_ numbirth
rename numwomen1544_ numwomen_1544
** Drop AO
sort Year Birthplace
by Year: gen num = _n
foreach var of varlist numbirth numwomen_1544 {
by Year: gen ao_`var' = `var'[42] if num==4
by Year: replace ao_`var' = `var'[30] if num==50
by Year: replace ao_`var' = `var'[18]+`var'[21] if num==79
by Year: replace ao_`var' = `var'[1] if num==10
by Year: replace ao_`var' = `var'[77]+`var'[13] if num==34
by Year: replace ao_`var' = `var'[85] if num==19
by Year: replace ao_`var' = `var'[31] if num==26
}

replace ao_numbirth = 0 if ao_numbirth==.
replace ao_numwomen_1544 = 0 if ao_numwomen_1544==.
gen numbirth_ao = numbirth-ao_numbirth
gen numwomen_1544_ao = numwomen_1544-ao_numwomen_1544
gen numbirth_withao = numbirth
gen numwomen_1544_withao = numwomen_1544
drop numbirth numwomen_1544
rename numbirth_ao numbirth
rename numwomen_1544_ao numwomen_1544

drop _merge
sort Birthplace_code
merge (Birthplace_code) using "$datatemp/birthplace_codes.dta"
keep if _merge==3
keep Birthplace_code Year numbirth numwomen_1544 loc
sort Birthplace_code Year
save "$datatemp/gfr_90_repl.dta", replace
